﻿using CMS.Utils;
using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Text;

namespace CMS.Data.Repository
{
    /// <summary>
    /// 仓库基类
    /// </summary>
    [NeverRegiste]
    public class BaseRepository
    {

        public static IConfigurationRoot Configuration { get; set; }

        public MySqlConnection GetMySqlConnection(bool open = true,
            bool convertZeroDatetime = false, bool allowZeroDatetime = false)
        {
            Configuration = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
#if RELEASE
                .AddJsonFile("appsettings.json")
#else
                .AddJsonFile("appsettings.Development.json")
#endif
                .Build();

            string connectionString = Configuration["AppSettings:MySqlConnectionString"];

            var connectionStringBuilder = new MySqlConnectionStringBuilder(connectionString)
            {
                AllowZeroDateTime = allowZeroDatetime,
                ConvertZeroDateTime = convertZeroDatetime
            };
            var conn = new MySqlConnection(connectionStringBuilder.ConnectionString);
            if (open) conn.Open();
            return conn;
        }

        /// <summary>
        /// 传入实体类获取插入sql
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="viewName"></param>
        /// <returns></returns>
        public string GetInsertSQL(object obj, string viewName)
        {
            //复数
            if (obj == null) return "";
            bool isList = false;
            List<Dictionary<string, object>> newList = null;
            Dictionary<string, object> newObj = null;
            StringBuilder mySQL = new StringBuilder();
            PropertyInfo[] pros = obj.GetType().GetProperties();
            foreach (PropertyInfo pro in pros)
            {
                if (pro.Name.ToLower() == "count" && obj.GetType().Name != "Dictionary`2")
                {
                    isList = true;
                    break;
                }
            }

            if (isList)
            {
                newList = obj.ToObject<List<Dictionary<string, object>>>();
                foreach (var item in newList)
                {
                    mySQL.AppendLine(GetInsertByDictToSQL(item, viewName));
                }
            }
            else
            {
                newObj = obj.ToObject<Dictionary<string, object>>();
                mySQL.Append(GetInsertByDictToSQL(newObj, viewName));
            }
            return mySQL.ToString();
        }

        /// <summary>
        /// 传入实体类获取更新sql
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="viewName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public string GetUpdateSQL(object obj, string viewName, string where)
        {
            bool isList = false;
            StringBuilder mySQL = new StringBuilder();
            List<Dictionary<string, object>> newList = null;
            Dictionary<string, object> newObj = null;
            PropertyInfo[] pros = obj.GetType().GetProperties();
            foreach (PropertyInfo pro in pros)
            {
                if (pro.Name.ToLower() == "count" && obj.GetType().Name != "Dictionary`2")
                {
                    isList = true;
                    break;
                }
            }
            if (isList)
            {
                newList = obj.ToObject<List<Dictionary<string, object>>>();
                foreach (var item in newList)
                {
                    mySQL.AppendLine(GetUpdateByDictToSQL(item, viewName, where));
                }
            }
            else
            {
                newObj = obj.ToObject<Dictionary<string, object>>();
                mySQL.Append(GetUpdateByDictToSQL(newObj, viewName, where));
            }
            return mySQL.ToString();
        }

        /// <summary>
        /// 传入实体类获取更新sql
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="viewName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public string GetUpdateSQL<T>(IList<T> obj, string viewName, string keyFieldName)
        {
            StringBuilder mySQL = new StringBuilder();
            List<Dictionary<string, object>> newList = null;
            PropertyInfo[] pros = obj.GetType().GetProperties();
            newList = obj.ToObject<List<Dictionary<string, object>>>();
            foreach (var item in newList)
            {
                mySQL.AppendLine(GetUpdateByDictToSQL(item, viewName, $"{keyFieldName} = {item[keyFieldName].ToMySQLValue()}"));
            }

            return mySQL.ToString();
        }

        /// <summary>
        /// 字典转insert的sql语句
        /// </summary>
        /// <param name="dict"></param>
        /// <param name="viewName"></param>
        /// <returns></returns>
        private string GetInsertByDictToSQL(IDictionary<string, object> dict, string viewName)
        {
            StringBuilder field = new StringBuilder();
            StringBuilder fieldValue = new StringBuilder();
            foreach (var item in dict)
            {
                double result = 0;
                var isNumber = double.TryParse(Convert.ToString(item.Value), out result);
                if (item.Value == null)
                {
                    continue;
                }
                else if (item.Value.GetType().Name != "String" && isNumber)     //纯数值类型强制更新
                {
                    field.Append(item.Key + ",");
                    fieldValue.Append(item.Value.ToMySQLValue() + ",");
                }
                else if (item.Value.GetType().Name == "Boolean")     //纯布尔类型强制更新
                {
                    field.Append(item.Key + ",");
                    fieldValue.Append(item.Value + ",");
                }
                else if (item.Value.GetType().Name == "DateTime" && item.Value.ToString() == DefaultForType(item.Value.GetType()))     //纯日期类型=默认值则=null
                {
                    field.Append(item.Key + ",");
                    fieldValue.Append("null,");
                }
                else if (item.Value.GetType().Name == "JObject")     //纯布尔类型强制更新
                {
                    field.Append(item.Key + ",");
                    fieldValue.Append(item.Value.ToMySQLValue() + ",");
                }
                else    //其余
                {
                    field.Append(item.Key + ",");
                    fieldValue.Append(item.Value.ToMySQLValue() + ",");
                }
            }
            field = field.Remove(field.ToString().Length - 1, 1);
            fieldValue = fieldValue.Remove(fieldValue.ToString().Length - 1, 1);
            string mySQL = string.Format("INSERT INTO " + viewName + "({0}) VALUES ({1});", field.ToString(), fieldValue.ToString());
            return mySQL;
        }

        /// <summary>
        /// 字典转update的sql语句
        /// </summary>
        /// <param name="dict"></param>
        /// <param name="viewName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        private string GetUpdateByDictToSQL(IDictionary<string, object> dict, string viewName, string where)
        {
            StringBuilder updateSQL = new StringBuilder();

            foreach (var item in dict)
            {
                double result = 0;
                var isNumber = double.TryParse(Convert.ToString(item.Value), out result);
                if (item.Value == null)     //则跳过
                {
                    continue;
                }
                else if (item.Value.GetType().Name != "String" && isNumber)     //纯数值类型强制更新
                {
                    updateSQL.Append(item.Key + "=" + item.Value.ToMySQLValue() + ",");
                }
                else if (item.Value.GetType().Name == "Boolean")     //纯布尔类型强制更新
                {
                    updateSQL.Append(item.Key + "=" + item.Value + ",");
                }
                else if (item.Value.GetType().Name == "DateTime" && item.Value.ToString() == DefaultForType(item.Value.GetType()))     //纯日期类型=默认值则=null
                {
                    updateSQL.Append(item.Key + "=null,");
                }
                else    //其余
                {
                    updateSQL.Append(item.Key + "=" + item.Value.ToMySQLValue() + ",");
                }
            }
            updateSQL = updateSQL.Remove(updateSQL.ToString().Length - 1, 1);
            string mySQL = string.Format(" update " + viewName + " set {0} where {1};", updateSQL.ToString(), where);
            return mySQL;
        }


        /// <summary>
        /// 得到分页SQL语句
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="pcTableName">表名/(sql)</param>
        /// <param name="orderBy">不填写，则默认排序。填写格式例如:字段1,字段2 desc</param>
        /// <returns>返回分页sql</returns>
        public static string GetPagingSQL(string strSelect, int pageIndex, int pageSize, string orderBy = " (select 0) ")
        {
            return $"{strSelect} order by {orderBy} limit { pageSize * (pageIndex - 1)}, {pageSize};";
        }

        /// <summary>
        /// 取得类型默认值
        /// </summary>
        /// <param name="targetType"></param>
        /// <returns></returns>
        private string DefaultForType(Type targetType)
        {
            if (targetType.IsValueType)
            {
                return Convert.ToString(Activator.CreateInstance(targetType));
            }
            else
            {
                return null;
            }
        }

    }
}
